package cn.sdjzu.exercise.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

	private static final String DB_NAME = "exercise.db";
	private static final int version = 1;

	/** 用户基本信息表 */
	public static final String TBL_USER = "user";
	public static final String TBL_USER_CARDNO = "cardNo";
	public static final String TBL_USER_NAME = "name";
	public static final String TBL_USER_SEX = "sex";
	public static final String TBL_USER_AGE = "age";
	public static final String TBL_USER_HEIGHT = "height";
	public static final String TBL_USER_WEIGHT = "weight";
	public static final String TBL_USER_FATCONTENT = "fatcontent";
	
	/** 用户扩展信息表 */
	public static final String TBL_USER_EXT = "user_ext";
	public static final String TBL_USER_EXT_CARDNO = TBL_USER_CARDNO;
	public static final String TBL_USER_EXT_GROUPS = "groups";
	public static final String TBL_USER_EXT_TIMES = "times";
	public static final String TBL_USER_EXT_MAXFORCE = "maxforce";
	public static final String TBL_USER_EXT_POSITION = "position";
	public static final String TBL_USER_EXT_PUSHTIME = "push_time";
	public static final String TBL_USER_EXT_RESETTIME = "reset_time";
	public static final String TBL_USER_EXT_SLEEPTIME = "sleep_time";
	public static final String TBL_USER_EXT_HOLDTIME = "hold_time";

	/** 用户锻炼信息表 */
	public static final String TBL_EXERCISE = "exercise";
	public static final String TBL_EXERCISE_CARDNO = TBL_USER_CARDNO;
	public static final String TBL_EXERCISE_MODE_ADDFORCE_TIMES = "mode_addforce_times";
	public static final String TBL_EXERCISE_MODE_ADDMUSCLE_TIMES = "mode_addmuscle_times";
	public static final String TBL_EXERCISE_MODE_BODYBUILDING_TIMES = "mode_bodybuilding_times";
	public static final String TBL_EXERCISE_MODE_REDUCEFAT_TIMES = "mode_reducefat_times";

	private String create_tbl_user_sql = "create table " + TBL_USER + "("
			+ TBL_USER_CARDNO + " primary key  , "
			+ TBL_USER_NAME + " varchar(60) not null , "
			+ TBL_USER_SEX + " varchar(1) not null , "
			+ TBL_USER_AGE + " varchar(3) not null , "
			+ TBL_USER_HEIGHT + " varchar(3) not null , "
			+ TBL_USER_WEIGHT + " varchar(3) not null , "
			+ TBL_USER_FATCONTENT + " varchar(3) not null );";
	
	private String create_tbl_user_ext_sql = "create table " + TBL_USER_EXT + "("
			+ TBL_USER_EXT_CARDNO + " primary key  , " 
			+ TBL_USER_EXT_GROUPS +  " varchar(3) , "
			+ TBL_USER_EXT_TIMES +  " varchar(3) , "
			+ TBL_USER_EXT_MAXFORCE + " varchar(3) , "
			+ TBL_USER_EXT_POSITION + " varchar(3) , "
			+ TBL_USER_EXT_PUSHTIME + " varchar(3) , "
			+ TBL_USER_EXT_RESETTIME + " varchar(3) , "
			+ TBL_USER_EXT_SLEEPTIME + " varchar(3) , "
			+ TBL_USER_EXT_HOLDTIME + " varchar(3) );";

	private String create_tbl_exercise_sql = "create table " + TBL_EXERCISE
			+ "(" + TBL_EXERCISE_CARDNO + " primary key , "
			+ TBL_EXERCISE_MODE_ADDFORCE_TIMES + " varchar(5) , "
			+ TBL_EXERCISE_MODE_ADDMUSCLE_TIMES + " varchar(5) , "
			+ TBL_EXERCISE_MODE_BODYBUILDING_TIMES + " varchar(5) , "
			+ TBL_EXERCISE_MODE_REDUCEFAT_TIMES + " varchar(5) );";

	public DBHelper(Context context) {
		super(context, DB_NAME, null, version);
		db = this.getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(create_tbl_user_sql);
		db.execSQL(create_tbl_user_ext_sql);
		db.execSQL(create_tbl_exercise_sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	private SQLiteDatabase db;

	public void close() {
		db.close();
	}

	/**
	 * 根据读出来的卡号，判断数据库中是否有保存这个卡号信息
	 * 
	 * @param cardNO
	 * @return
	 */
	public boolean isExistCardNoInDB(String tblNm, String cardNO) {
		String sql = "SELECT * FROM " + tblNm + " WHERE "
				+ DBHelper.TBL_USER_CARDNO + "=?";

		Cursor c = db.rawQuery(sql, new String[] { cardNO });
		if (c == null || !c.moveToFirst()) {
			c.close();
			return false;
		} else {
			c.close();
			return true;
		}
	}

	public long insertData2DB(String tblNm, ContentValues values) {
		return db.insert(tblNm, null, values);
	}

	/**
	 * 更新User信息
	 * 
	 * @param values
	 * @return
	 */
	public long updateUserData(ContentValues values) {
		return db.update(DBHelper.TBL_USER, values, DBHelper.TBL_USER_CARDNO
				+ "=?",
				new String[] { String.valueOf(values.get(TBL_USER_CARDNO)) });
	}

	/**
	 * 更新UserExt信息
	 * 
	 * @param values
	 * @return
	 */
	public long updateUserExtData(ContentValues values) {
		return db.update(DBHelper.TBL_USER_EXT, values,
				DBHelper.TBL_USER_EXT_CARDNO + "=?",
				new String[] { String.valueOf(values.get(TBL_USER_CARDNO)) });
	}
	
	/**
	 * 
	 * @param tblNm
	 *            数据库的表名
	 * @param tbl_field
	 *            要取得的字段名
	 * @param cardNO
	 *            作为条件的卡号
	 * @return
	 */
	public String getDataFromDB(String tblNm, String tbl_field, String cardNO) {
		String sql = "SELECT * FROM " + tblNm + " WHERE "
				+ DBHelper.TBL_USER_CARDNO + "=?";
		if (cardNO == null) {
			cardNO = "000000";
		}
		Cursor c = db.rawQuery(sql, new String[] { cardNO });
		if (c != null && c.moveToFirst()) {
			String rtn = c.getString(c.getColumnIndex(tbl_field));
			c.close();
			return rtn;
		}
		return "";
	}
}